package com.newcapec.dao.impl;

import com.newcapec.bean.ColumnEntity;
import com.newcapec.bean.UserEntity;
import com.newcapec.dao.ColumnDao;
import com.newcapec.util.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description:
 * @Author:yangshupeng
 * @Date: 2022/3/14 8:29
 * @Version:1.0
 */
public class ColumnDaoImpl implements ColumnDao {
    private Connection conn;
    private Statement stmt;
    private PreparedStatement pstmt;
    private ResultSet rs;
    @Override
    public boolean addColumn(ColumnEntity columnEntity) {
        //设置一个标识符，用来判断是否添加成功
        boolean isFlag = false;
        try {
            //获取连接
            conn = JDBCUtil.getConn();
            //创建添加的sql语句
            String sql = "insert into news_chnl (name,sort,crtime,uptime) values (?,?,now(),now())";
            //创建sql语句的对象
            pstmt = conn.prepareStatement(sql);

            //给占位符?设置值
            pstmt.setString(1,columnEntity.getName());
            pstmt.setString(2,String.valueOf(columnEntity.getSort()));
            //执行sql语句，并返回一个int类型的值（数据库中有几行数据被影响）
            int i = pstmt.executeUpdate();

            if (i != 0){
                isFlag = true;//添加成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            JDBCUtil.closeSource(conn,pstmt,null);
        }
        return isFlag;
    }

    @Override
    public boolean deleteColumn(String columnId) {
        //设置一个标识符，用来判断是否添加成功
        boolean isFlag = false;
        try {
            //获取连接
            conn = JDBCUtil.getConn();
            //创建添加的sql语句
            String sql = "delete from news_chnl where id = ?";
            //创建sql语句的对象
            pstmt = conn.prepareStatement(sql);

            //给占位符?设置值
            pstmt.setInt(1,Integer.parseInt(columnId));

            //执行sql语句，并返回一个int类型的值（数据库中有几行数据被影响）
            int i = pstmt.executeUpdate();

            if (i != 0){
                isFlag = true;//删除成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            JDBCUtil.closeSource(conn,pstmt,null);
        }
        return isFlag;
    }

    @Override
    public boolean updateColumn(ColumnEntity columnEntity) {
        //设置一个标识符，用来判断是否添加成功
        boolean isFlag = false;
        try {
            //获取连接
            conn = JDBCUtil.getConn();
            //创建添加的sql语句
            String sql = "update news_chnl set name = ?,sort = ?,uptime = now() where id = ?";
            //创建sql语句的对象
            pstmt = conn.prepareStatement(sql);

            //给占位符?设置值
            pstmt.setString(1,columnEntity.getName());
            pstmt.setInt(2,columnEntity.getSort());
            pstmt.setInt(3,columnEntity.getId());

            //执行sql语句，并返回一个int类型的值（数据库中有几行数据被影响）
            int i = pstmt.executeUpdate();

            if (i != 0){
                isFlag = true;//修改成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            JDBCUtil.closeSource(conn,pstmt,null);
        }
        return isFlag;
    }

    @Override
    public List<ColumnEntity> query(int offset, int pageSize, ColumnEntity colum) {
        List<ColumnEntity> columnList = new ArrayList<>();
        conn = JDBCUtil.getConn();
        try {
            String sql = "select id,name,sort,crtime,uptime from news_chnl ";
            if (colum != null && colum.getName() != null){
                sql += "where name like ?";
            }
            sql += "limit ?,?";
            pstmt = conn.prepareStatement(sql);
            if (colum != null && colum.getName() != null){
                pstmt.setString(1,"%" + colum.getName() + "%");
                pstmt.setInt(2,offset);
                pstmt.setInt(3,pageSize);
            }else {
                pstmt.setInt(1,offset);
                pstmt.setInt(2,pageSize);
            }
            rs = pstmt.executeQuery();
            while (rs.next()){
                ColumnEntity columnEntity = new ColumnEntity();
                columnEntity.setId(rs.getInt("id"));
                columnEntity.setName(rs.getString("name"));
                columnEntity.setSort(rs.getInt("sort"));
                columnEntity.setCrtime(rs.getString("crtime"));
                columnEntity.setUptime(rs.getString("uptime"));

                columnList.add(columnEntity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,pstmt,rs);
        }
        return columnList;
    }

    @Override
    public List<ColumnEntity> queryAll() {
        List<ColumnEntity> columnList = new ArrayList<>();
        conn = JDBCUtil.getConn();
        try {
            String sql = "select id,name,sort,crtime,uptime from news_chnl ";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()){
                ColumnEntity columnEntity = new ColumnEntity();
                columnEntity.setId(rs.getInt("id"));
                columnEntity.setName(rs.getString("name"));
                columnEntity.setSort(rs.getInt("sort"));
                columnEntity.setCrtime(rs.getString("crtime"));
                columnEntity.setUptime(rs.getString("uptime"));

                columnList.add(columnEntity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,pstmt,rs);
        }
        return columnList;
    }

    @Override
    public ColumnEntity queryColumnById(String id) {
        ColumnEntity columnEntity = null;//没查到返回一个null
        conn = JDBCUtil.getConn();
        try {
            String sql = "select id,name,sort,crtime,uptime from news_chnl  where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,Integer.parseInt(id));
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()){
                columnEntity = new ColumnEntity();
                columnEntity.setId(rs.getInt("id"));
                columnEntity.setName(rs.getString("name"));
                columnEntity.setSort(rs.getInt("sort"));
                columnEntity.setCrtime(rs.getString("crtime"));
                columnEntity.setUptime(rs.getString("uptime"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,stmt,rs);
        }
        return columnEntity;
    }

    @Override
    public ColumnEntity queryColumnByName(String name) {
        ColumnEntity columnEntity = null;//没查到返回一个null
        conn = JDBCUtil.getConn();
        try {
            String sql = "select id,name,sort,crtime,uptime from news_chnl  where name = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,name);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()){
                columnEntity = new ColumnEntity();
                columnEntity.setId(rs.getInt("id"));
                columnEntity.setName(rs.getString("name"));
                columnEntity.setSort(rs.getInt("sort"));
                columnEntity.setCrtime(rs.getString("crtime"));
                columnEntity.setUptime(rs.getString("uptime"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,stmt,rs);
        }
        return columnEntity;
    }

    @Override
    public int getAllCount(String name) {
        conn = JDBCUtil.getConn();
        try {
            String sql = "select count(1) from news_chnl ";
            if (name != null){
                sql += "where name like ?";
            }


            pstmt = conn.prepareStatement(sql);
            if (name != null){
                pstmt.setString(1,"%" + name + "%");
            }
            rs = pstmt.executeQuery();
            if (rs.next()){
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,stmt,rs);
        }
        return 0;
    }


}
